package com.bobo.insertSale;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.bobo.vo.Sale;
import com.mysql.jdbc.PreparedStatement;

public class InsertSale {
	public static int insert(List<Sale> sales) {
		Connection conn = null;
		boolean inserted = true;
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/sp?"
					+ "user=root&password=root");
		} catch (SQLException e1) {
			inserted = false;
			e1.printStackTrace();
		}
		// 开时时间
		Long begin = new Date().getTime();
		// sql前缀
		String prefix = "INSERT INTO sale(id, userId, productId, ccost, saleCount,`date`) VALUES ";
		try {
			// 保存sql后缀
			StringBuffer suffix = new StringBuffer();
			// 设置事务为非自动提交
			conn.setAutoCommit(false);
			// Statement st = conn.createStatement();
			// 比起st，pst会更好些
			PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");
			//list sales
			// 外层循环，总提交事务次数
//			INSERT INTO sale(id, userId, productId, ccost, saleCount,`date`) 
//			VALUE(NULL,#{userId},#{productId},#{ccost},#{saleCount},#{date});
			int index = sales.size();
			int temp = 0;
			for(int i = 0; i < index; i++) {
				temp++;
				suffix.append("(" + "NULL," +
						sales.get(i).getUserId() + "," +
						sales.get(i).getProductId() + "," +
						 "'" + sales.get(i).getCcost() +  "'" + "," +
						 "'" + sales.get(i).getSaleCount() + "'" + "," +
						 "'" + sales.get(i).getDate() + "'" +
						"),");
				
				if(temp % 10000 == 0) {
					// 构建完整sql
					String sql = prefix + suffix.substring(0, suffix.length() - 1);
System.out.println(sql);
					// 添加执行sql
					pst.addBatch(sql);
					// 执行操作
					pst.executeBatch();
					// 提交事务
					conn.commit();
					// 清空上一次添加的数据
					suffix = new StringBuffer();
				}
				
				if(temp >=120000 && temp ==sales.size() ) {
					// 构建完整sql
					String sql = prefix + suffix.substring(0, suffix.length() - 1);
System.out.println(sql);
					// 添加执行sql
					pst.addBatch(sql);
					// 执行操作
					pst.executeBatch();
					// 提交事务
					conn.commit();
					// 清空上一次添加的数据
					suffix = new StringBuffer();
				}
			}
			// 头等连接
			pst.close();
			conn.close();
		} catch (SQLException e) {
			inserted = false;
			e.printStackTrace();
		}
		// 结束时间
		Long end = new Date().getTime();
		// 耗时
		System.out.println("cast : " + (end - begin) / 1000 + " ms");
		return sales.size();
	}
}
